#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
将工作量评估表.md转换为Excel格式的脚本
"""

import pandas as pd
import re
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
import os

def parse_markdown_table(md_content):
    """
    解析markdown表格内容，返回DataFrame
    """
    lines = md_content.split('\n')

    # 找到表格开始的位置
    table_start = -1
    for i, line in enumerate(lines):
        if '| 序号 |' in line and '模块（需求大类）' in line:
            table_start = i
            break

    if table_start == -1:
        raise ValueError("未找到表格内容")

    # 提取表格行
    table_lines = []
    for i in range(table_start, len(lines)):
        line = lines[i].strip()
        if line.startswith('|') and line.endswith('|'):
            table_lines.append(line)
        elif line == '':  # 遇到空行停止
            break

    # 解析表头
    header_line = table_lines[0]
    headers = [col.strip() for col in header_line.split('|')[1:-1]]

    # 解析数据行
    data = []
    for line in table_lines[1:]:
        if '|' in line:
            row_data = [col.strip() for col in line.split('|')[1:-1]]
            if len(row_data) == len(headers) and row_data[0] != '------':  # 跳过分隔线
                data.append(row_data)

    # 创建DataFrame
    df = pd.DataFrame(data, columns=headers)

    # 处理合计行（如果存在）
    summary_row = None
    for line in lines:
        if '人天合计' in line and '|' in line:
            summary_parts = [col.strip() for col in line.split('|')[1:-1]]
            if len(summary_parts) >= len(headers):
                summary_row = summary_parts[:len(headers)]
            break

    return df, summary_row, headers

def format_excel_sheet(ws, df, summary_row, headers):
    """
    格式化Excel工作表
    """
    # 设置列宽
    column_widths = {
        '序号': 8,
        '模块（需求大类）': 20,
        '功能（需求小类）': 25,
        '详细描述': 40,
        '备注': 25,
        '判定类型（标准功能/接口/定制功能）': 25,
        '合计人天': 12,
        '管理人天': 12,
        '需求人天': 12,
        'UI人天': 10,
        '架构师': 10,
        '前端人天': 12,
        '后端人天': 12,
        '测试人天': 12
    }

    for col_num, header in enumerate(headers, 1):
        col_letter = get_column_letter(col_num)
        width = column_widths.get(header, 15)
        ws.column_dimensions[col_letter].width = width

    # 设置样式
    header_font = Font(name='微软雅黑', size=10, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    summary_font = Font(name='微软雅黑', size=10, bold=True, color='FF0000')
    summary_fill = PatternFill(start_color='FFE6E6', end_color='FFE6E6', fill_type='solid')
    data_font = Font(name='微软雅黑', size=9)
    border = Border(
        left=Side(style='thin', color='000000'),
        right=Side(style='thin', color='000000'),
        top=Side(style='thin', color='000000'),
        bottom=Side(style='thin', color='000000')
    )
    alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

    # 添加合计行（如果存在）
    current_row = 1
    if summary_row:
        for col_num, value in enumerate(summary_row, 1):
            cell = ws.cell(row=current_row, column=col_num, value=value)
            cell.font = summary_font
            cell.fill = summary_fill
            cell.border = border
            cell.alignment = Alignment(horizontal='center', vertical='center')
        current_row += 1

    # 添加表头
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=current_row, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = border
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    current_row += 1

    # 添加数据行
    data_start_row = current_row
    for _, row in df.iterrows():
        for col_num, (header, value) in enumerate(zip(headers, row), 1):
            cell = ws.cell(row=current_row, column=col_num, value=value)
            cell.font = data_font
            cell.border = border
            cell.alignment = alignment

            # 数值列居中对齐
            if header in ['合计人天', '管理人天', '需求人天', 'UI人天', '架构师', '前端人天', '后端人天', '测试人天']:
                if str(value).isdigit():
                    cell.value = int(value)
                    cell.alignment = Alignment(horizontal='center', vertical='center')
                    # 设置背景色以突出数值列
                    cell.fill = PatternFill(start_color='F0F8FF', end_color='F0F8FF', fill_type='solid')
                else:
                    cell.alignment = Alignment(horizontal='center', vertical='center')
                    cell.fill = PatternFill(start_color='F0F8FF', end_color='F0F8FF', fill_type='solid')

            # 序号列居中
            if header == '序号':
                cell.alignment = Alignment(horizontal='center', vertical='center')
        current_row += 1

    # 添加自动计算的合计行
    total_row = current_row
    ws.cell(row=total_row, column=1, value='合计')
    ws.cell(row=total_row, column=1).font = summary_font
    ws.cell(row=total_row, column=1).fill = summary_fill
    ws.cell(row=total_row, column=1).border = border
    ws.cell(row=total_row, column=1).alignment = Alignment(horizontal='center', vertical='center')

    # 为数值列添加求和公式
    numeric_headers = ['合计人天', '管理人天', '需求人天', 'UI人天', '架构师', '前端人天', '后端人天', '测试人天']
    for col_num, header in enumerate(headers, 1):
        if header in numeric_headers:
            col_letter = get_column_letter(col_num)
            formula = f'=SUM({col_letter}{data_start_row}:{col_letter}{total_row-1})'
            cell = ws.cell(row=total_row, column=col_num, value=formula)
            cell.font = summary_font
            cell.fill = summary_fill
            cell.border = border
            cell.alignment = Alignment(horizontal='center', vertical='center')

    # 设置其他列的边框
    for col_num in range(2, len(headers) + 1):
        if headers[col_num-1] not in numeric_headers:
            cell = ws.cell(row=total_row, column=col_num, value='')
            cell.font = summary_font
            cell.fill = summary_fill
            cell.border = border
            cell.alignment = Alignment(horizontal='center', vertical='center')

def main():
    """
    主函数：读取markdown文件并转换为Excel
    """
    md_file = '/Users/xueyuheng/workspace/ubbc/ubbc5/工作量评估表.md'
    excel_file = '/Users/xueyuheng/workspace/ubbc/ubbc5/工作量评估表.xlsx'

    # 检查文件是否存在
    if not os.path.exists(md_file):
        print(f"错误：找不到文件 {md_file}")
        return

    try:
        # 读取markdown文件
        with open(md_file, 'r', encoding='utf-8') as f:
            md_content = f.read()

        # 解析表格
        df, summary_row, headers = parse_markdown_table(md_content)

        # 创建Excel工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = '工作量评估表'

        # 格式化工作表
        format_excel_sheet(ws, df, summary_row, headers)

        # 保存Excel文件
        wb.save(excel_file)
        print(f"成功将 {md_file} 转换为 {excel_file}")
        print(f"共处理了 {len(df)} 行数据")

        # 显示统计信息
        if summary_row:
            print(f"合计行：{summary_row}")

    except Exception as e:
        print(f"转换过程中出现错误：{e}")

if __name__ == '__main__':
    main()
